from SystematicFactors.General import *
import os
import Core.Gadget as Gadget

# ---债券市场---
# 理财市场收益率
def Calc_Licai_Market(database, datetime1, datetime2):
    df1 = EDB('M0074411', datetime1, datetime2, 'Licai_TotalMkt_1M')  # 理财产品预期年收益率:人民币:全市场:1个月
    df2 = EDB('M0074417', datetime1, datetime2, 'Licai_TotalMkt_1Y')  # 理财产品预期年收益率:人民币:全市场:1年
    Save_Systematic_Factor_To_Database(database, df1, 'Licai_TotalMkt_1M')
    Save_Systematic_Factor_To_Database(database, df2, 'Licai_TotalMkt_1Y')


# 信托市场收益率
def Calc_Trust(database, datetime1, datetime2):
    #
    df = EDB('M0085865', datetime1, datetime2)  # 信托产品预期年收益率:非证券投资类信托平均:1-2年(含)
    df["dif"] = df["M0085865"].diff(1)
    df["Report_Date"] = df.index
    Fill_ReleaseDate(df, lag_release_month=1, release_day=15)
    #
    # print(df)
    Save_Systematic_Factor_To_Database(database, df, save_name='Trust_1Y2Y', field_name='M0085865')
    Save_Systematic_Factor_To_Database(database, df, save_name='Trust_1Y2Y_Monthly_Dif', field_name='dif')


# 债券市场利率
def Calc_Bond_Market_Rate(database, datetime1, datetime2):
    # ---国债---
    df_tbond_1yr = EDB('S0059744', datetime1, datetime2, 'Tbond_YTM_1Y')  # 中债国债到期收益率:1年
    df_tbond_5yr = EDB('S0059747', datetime1, datetime2, 'Tbond_YTM_5Y')  # 中债国债到期收益率:5年
    df_tbond_10yr = EDB('S0059749', datetime1, datetime2, 'Tbond_YTM_10Y')  # 中债国债到期收益率:10年  #  M1000166
    #
    Save_Systematic_Factor_To_Database(database, df_tbond_1yr, 'Tbond_YTM_1Y')
    Save_Systematic_Factor_To_Database(database, df_tbond_5yr, 'Tbond_YTM_5Y')
    Save_Systematic_Factor_To_Database(database, df_tbond_10yr, 'Tbond_YTM_10Y')

    # ---国开债--- 20220207
    # 中债国开债到期收益率:10年
    df_gk_bond_10yr = EDB('M1004271', datetime1, datetime2, "GK_Bond_YTM_10Y")
    Save_Systematic_Factor_To_Database(database, df_gk_bond_10yr, 'GK_Bond_YTM_10Y')

    # ---Term Spread---
    df = pd.merge(df_tbond_1yr, df_tbond_10yr, how="inner", left_index=True, right_index=True)
    df["Term_Spread_10Y1Y"] = df["Tbond_YTM_10Y"] - df["Tbond_YTM_1Y"]
    Save_Systematic_Factor_To_Database(database, df, 'Term_Spread_10Y1Y')
    #
    df = pd.merge(df_tbond_5yr, df_tbond_10yr, how="inner", left_index=True, right_index=True)
    df["Term_Spread_10Y5Y"] = df["Tbond_YTM_10Y"] - df["Tbond_YTM_5Y"]
    Save_Systematic_Factor_To_Database(database, df, 'Term_Spread_10Y5Y')

    # ---信用债---
    # ---Credit Spread AA+ ---
    df_aap_1yr = EDB('S0059843', datetime1, datetime2, 'CorpBond_AAP_YTM_1Y')  # 中债企业债到期收益率(AA+):1年
    df_aap_5yr = EDB('S0059846', datetime1, datetime2, 'CorpBond_AAP_YTM_5Y')  # 中债企业债到期收益率(AA+):5年
    df_aap_10yr = EDB('S0059848', datetime1, datetime2, 'CorpBond_AAP_YTM_10Y')  # 中债企业债到期收益率(AA+):10年
    #
    Save_Systematic_Factor_To_Database(database, df_aap_1yr, 'CorpBond_AAP_YTM_1Y')
    Save_Systematic_Factor_To_Database(database, df_aap_5yr, 'CorpBond_AAP_YTM_5Y')
    Save_Systematic_Factor_To_Database(database, df_aap_10yr, 'CorpBond_AAP_YTM_10Y')
    #
    df = pd.merge(df_tbond_1yr, df_aap_1yr, how="inner", left_index=True, right_index=True)
    df["Credit_Spread_AAP_1Y"] = df["CorpBond_AAP_YTM_1Y"] - df["Tbond_YTM_1Y"]
    Save_Systematic_Factor_To_Database(database, df, 'Credit_Spread_AAP_1Y')
    #
    df = pd.merge(df_tbond_5yr, df_aap_5yr, how="inner", left_index=True, right_index=True)
    df["Credit_Spread_AAP_5Y"] = df["CorpBond_AAP_YTM_5Y"] - df["Tbond_YTM_5Y"]
    Save_Systematic_Factor_To_Database(database, df, 'Credit_Spread_AAP_5Y')
    #
    df = pd.merge(df_tbond_10yr, df_aap_10yr, how="inner", left_index=True, right_index=True)
    df["Credit_Spread_AAP_10Y"] = df["CorpBond_AAP_YTM_10Y"] - df["Tbond_YTM_10Y"]
    Save_Systematic_Factor_To_Database(database, df, 'Credit_Spread_AAP_10Y')

    # ---信用债---
    # --- Credit Spread AAA---
    df_aaa_1yr = EDB('S0059771', datetime1, datetime2, 'CorpBond_AAA_YTM_1Y')  # 中债企业债到期收益率(AAA):1年
    df_aaa_5yr = EDB('S0059774', datetime1, datetime2, 'CorpBond_AAA_YTM_5Y')  # 中债企业债到期收益率(AAA):5年
    df_aaa_10yr = EDB('S0059776', datetime1, datetime2, 'CorpBond_AAA_YTM_10Y')  # 中债企业债到期收益率(AAA):10年
    #
    Save_Systematic_Factor_To_Database(database, df_aaa_1yr, 'CorpBond_AAA_YTM_1Y')
    Save_Systematic_Factor_To_Database(database, df_aaa_5yr, 'CorpBond_AAA_YTM_5Y')
    Save_Systematic_Factor_To_Database(database, df_aaa_10yr, 'CorpBond_AAA_YTM_10Y')
    #
    df = pd.merge(df_tbond_1yr, df_aaa_1yr, how="inner", left_index=True, right_index=True)
    df["Credit_Spread_1Y"] = df["CorpBond_AAA_YTM_1Y"] - df["Tbond_YTM_1Y"]
    Save_Systematic_Factor_To_Database(database, df, 'Credit_Spread_1Y')
    #
    df = pd.merge(df_tbond_5yr, df_aaa_5yr, how="inner", left_index=True, right_index=True)
    df["Credit_Spread_5Y"] = df["CorpBond_AAA_YTM_5Y"] - df["Tbond_YTM_5Y"]
    Save_Systematic_Factor_To_Database(database, df, 'Credit_Spread_5Y')
    #
    df = pd.merge(df_tbond_10yr, df_aaa_10yr, how="inner", left_index=True, right_index=True)
    df["Credit_Spread_10Y"] = df["CorpBond_AAA_YTM_10Y"] - df["Tbond_YTM_10Y"]
    Save_Systematic_Factor_To_Database(database, df, 'Credit_Spread_10Y')


#
def Calc_BondMarket_20200513(database, datetime1, datetime2):
    # --- Term Spread---
    df_tbond_1yr = EDB('S0059744', datetime1, datetime2, 'Tbond_YTM_1Y')  # 中债国债到期收益率:1年
    df_tbond_5yr = EDB('S0059747', datetime1, datetime2, 'Tbond_YTM_5Y')  # 中债国债到期收益率:5年
    df_tbond_10yr = EDB('S0059749', datetime1, datetime2, 'Tbond_YTM_10Y')  # 中债国债到期收益率:10年
    #
    df = pd.merge(df_tbond_1yr, df_tbond_10yr, how="inner", left_index=True, right_index=True)
    df["Term_Spread_10Y1Y"] = df["Tbond_YTM_10Y"] - df["Tbond_YTM_1Y"]
    Save_Systematic_Factor_To_Database(database, df, 'Term_Spread_10Y1Y')
    #
    df = pd.merge(df_tbond_5yr, df_tbond_10yr, how="inner", left_index=True, right_index=True)
    df["Term_Spread_10Y5Y"] = df["Tbond_YTM_10Y"] - df["Tbond_YTM_5Y"]
    Save_Systematic_Factor_To_Database(database, df, 'Term_Spread_10Y5Y')

    # ---Credit Spread AA+ ---
    df_aap_1yr = EDB('S0059843', datetime1, datetime2, 'CorpBond_AAP_YTM_1Y')  # 中债企业债到期收益率(AA+):1年
    df_aap_5yr = EDB('S0059846', datetime1, datetime2, 'CorpBond_AAP_YTM_5Y')  # 中债企业债到期收益率(AA+):5年
    df_aap_10yr = EDB('S0059848', datetime1, datetime2, 'CorpBond_AAP_YTM_10Y')  # 中债企业债到期收益率(AA+):10年
    #
    Save_Systematic_Factor_To_Database(database, df_aap_1yr, 'CorpBond_AAP_YTM_1Y')
    Save_Systematic_Factor_To_Database(database, df_aap_5yr, 'CorpBond_AAP_YTM_5Y')
    Save_Systematic_Factor_To_Database(database, df_aap_10yr, 'CorpBond_AAP_YTM_10Y')

    df = pd.merge(df_tbond_1yr, df_aap_1yr, how="inner", left_index=True, right_index=True)
    df["Credit_Spread_AAP_1Y"] = df["CorpBond_AAP_YTM_1Y"] - df["Tbond_YTM_1Y"]
    Save_Systematic_Factor_To_Database(database, df, 'Credit_Spread_AAP_1Y')
    #
    df = pd.merge(df_tbond_5yr, df_aap_5yr, how="inner", left_index=True, right_index=True)
    df["Credit_Spread_AAP_5Y"] = df["CorpBond_AAP_YTM_5Y"] - df["Tbond_YTM_5Y"]
    Save_Systematic_Factor_To_Database(database, df, 'Credit_Spread_AAP_5Y')
    #
    df = pd.merge(df_tbond_10yr, df_aap_10yr, how="inner", left_index=True, right_index=True)
    df["Credit_Spread_AAP_10Y"] = df["CorpBond_AAP_YTM_10Y"] - df["Tbond_YTM_10Y"]
    Save_Systematic_Factor_To_Database(database, df, 'Credit_Spread_AAP_10Y')

    # --- Credit Spread AAA---
    df_aaa_1yr = EDB('S0059771', datetime1, datetime2, 'CorpBond_AAA_YTM_1Y')  # 中债企业债到期收益率(AAA):1年
    df_aaa_5yr = EDB('S0059774', datetime1, datetime2, 'CorpBond_AAA_YTM_5Y')  # 中债企业债到期收益率(AAA):5年
    df_aaa_10yr = EDB('S0059776', datetime1, datetime2, 'CorpBond_AAA_YTM_10Y')  # 中债企业债到期收益率(AAA):10年
    #
    Save_Systematic_Factor_To_Database(database, df_aaa_1yr, 'CorpBond_AAA_YTM_1Y')
    Save_Systematic_Factor_To_Database(database, df_aaa_5yr, 'CorpBond_AAA_YTM_5Y')
    Save_Systematic_Factor_To_Database(database, df_aaa_10yr, 'CorpBond_AAA_YTM_10Y')
    #
    df = pd.merge(df_tbond_1yr, df_aaa_1yr, how="inner", left_index=True, right_index=True)
    df["Credit_Spread_1Y"] = df["CorpBond_AAA_YTM_1Y"] - df["Tbond_YTM_1Y"]
    Save_Systematic_Factor_To_Database(database, df, 'Credit_Spread_1Y', "a_sys_factor_market")
    #
    df = pd.merge(df_tbond_5yr, df_aaa_5yr, how="inner", left_index=True, right_index=True)
    df["Credit_Spread_5Y"] = df["CorpBond_AAA_YTM_5Y"] - df["Tbond_YTM_5Y"]
    Save_Systematic_Factor_To_Database(database, df, 'Credit_Spread_5Y')
    #
    df = pd.merge(df_tbond_10yr, df_aaa_10yr, how="inner", left_index=True, right_index=True)
    df["Credit_Spread_10Y"] = df["CorpBond_AAA_YTM_10Y"] - df["Tbond_YTM_10Y"]
    Save_Systematic_Factor_To_Database(database, df, 'Credit_Spread_10Y')


def Calc_Credit_Margin_AAA(database, datetime1, datetime2):
    #
    df1 = Query_Data(database, 'S0059771', datetime1, datetime2)  # 中债企业债到期收益率(AAA):1年
    df2 = Query_Data(database, 'S0059744', datetime1, datetime2)  # 中债国债到期收益率:1年
    df39 = pd.concat([df1, df2], axis=1, sort=True)
    #
    df39.index = pd.to_datetime(df39.index)
    df39["Release_Date"] = df39.index
    df_date = df39.resample("M").last()
    df_date = df_date[["Release_Date"]]
    df = df39.resample("M").mean()
    df = pd.merge(df, df_date, how="left", left_index=True, right_index=True)
    #
    df['div'] = df['S0059771'] / df['S0059744'] - 1
    df['factor'] = df['div'].diff()
    df['Report_Date'] = df.index
    #
    print(df)
    df = df[:-1]
    # return(df39,'AAA_Tbond_Margin_Dif')
    Save_Systematic_Factor_To_Database(database, df, save_name='AAA_Tbond_Margin', field_name='div')
    df.dropna(subset=["factor"], inplace=True)
    Save_Systematic_Factor_To_Database(database, df, save_name='AAA_Tbond_Margin_Monthly_Dif', field_name='factor')


def Calc_Credit_Margin_Guokai(database, datetime1, datetime2):

    df1 = Query_Data(database, 'M1004263', datetime1, datetime2)  # 中债国开债到期收益率:1年
    df2 = Query_Data(database, 'S0059744', datetime1, datetime2)  # 中债国债到期收益率:1年
    df38 = pd.concat([df1, df2], axis=1, sort=True)
    #
    df38.index = pd.to_datetime(df38.index)
    df38["Release_Date"] = df38.index
    df_date = df38.resample("M").last()
    df_date = df_date[["Release_Date"]]
    df = df38.resample("M").mean()
    df = pd.merge(df, df_date, how="left", left_index=True, right_index=True)
    #
    df['div'] = df['M1004263'] / df['S0059744'] - 1
    df['factor'] = df['div'].diff()
    df['Report_Date'] = df.index
    print(df)
    df = df[:-1]
    # return(df38,'GKBond_Tbond_Margin_Dif')
    Save_Systematic_Factor_To_Database(database, df, save_name='GKBond_Tbond_Margin', field_name='div')
    df.dropna(subset=["factor"], inplace=True)
    Save_Systematic_Factor_To_Database(database, df, save_name='GKBond_Tbond_Margin_Monthly_Dif', field_name='factor')


# creditspd10y
def Calc_Credit_Spread(database, datetime1, datetime2):

    df1 = Query_Data(database, 'S0059776',datetime1,datetime2)  # 中债企业债到期收益率(AAA):10年
    df2 = Query_Data(database, 'S0059749',datetime1,datetime2)  # 国债到期收益率 10年
    df31 = pd.merge(df1, df2, how="inner", left_index=True, right_index=True)
    df31.index = pd.to_datetime(df31.index)
    df31['sub'] = df31['S0059776'] - df31['S0059749']
    df31['Release_Date'] = df31.index

    # df31['yearmonth'] = df31.index.map(lambda x: 100*x.year+x.month)
    # df31 = df31.groupby('yearmonth').mean()
    df31_2 = df31.resample("M").last()
    df31_2 = df31_2[["Release_Date"]]
    #
    df31 = df31.resample("M").mean()
    df31 = pd.merge(df31, df31_2, how="left", left_index=True, right_index=True)

    df31['factor'] = df31['sub'].diff()
    df31['Report_Date'] = df31.index
    df31 = df31[:-1]
    print(df31)

    # return(df32,'Credit_Spread_10Y_Monthly')
    Save_Systematic_Factor_To_Database(database, df31, save_name='Credit_Spread_10Y_Avg_Monthly', field_name='sub')
    df31.dropna(subset=["factor"], inplace=True)
    Save_Systematic_Factor_To_Database(database, df31, save_name='Credit_Spread_10Y_Avg_Monthly_Dif',
                                       field_name='factor')


def Calc_Term_Spread(database, datetime1, datetime2):
    # 日数据求月平均
    df1 = Query_Data(database, 'S0059749', datetime1,datetime2)  # 中债国债到期收益率:10年
    df2 = Query_Data(database, 'S0059744', datetime1,datetime2)  # 中债国债到期收益率:1年
    # df31 = pd.concat([df1, df2], axis=1)
    df31 = pd.merge(df1, df2, how="inner", left_index=True, right_index=True)
    df31.index = pd.to_datetime(df31.index)
    df31['sub'] = df31['S0059749'] - df31['S0059744']
    df31['Release_Date'] = df31.index

    # df31['yearmonth'] = df31.index.map(lambda x: 100*x.year+x.month)
    # df31 = df31.groupby('yearmonth').mean()
    df31_2 = df31.resample("M").last()
    df31_2 = df31_2[["Release_Date"]]
    #
    df31 = df31.resample("M").mean()
    df31 = pd.merge(df31, df31_2, how="left", left_index=True, right_index=True)

    df31['factor'] = df31['sub'].diff()
    df31['Report_Date'] = df31.index
    df31 = df31[:-1]
    print(df31)
    # return(df31,'Term_Spread_10Y1Y_Monthly')
    Save_Systematic_Factor_To_Database(database, df31, save_name='Term_Spread_10Y1Y_Avg_Monthly', field_name='sub')
    df31.dropna(subset=["factor"], inplace=True)
    Save_Systematic_Factor_To_Database(database, df31, save_name='Term_Spread_10Y1Y_Avg_Monthly_Dif',
                                       field_name='factor')




if __name__ == '__main__':
    #
    path_filename = os.getcwd() + "\..\Config\config_local.json"
    database = Config.create_database(database_type="MySQL", config_file=path_filename, config_field="MySQL")

    w.start()

    datetime1 = datetime.datetime(2023, 11, 1)
    datetime2 = datetime.datetime(2024, 4, 26)
    #